/*
 * SQLiteDb.cpp
 *
 *  Created on: 2011-4-18
 *      Author: wanghuan
 *  
 *  SQLite C++ API usage:
 *  
 *  1. Get an instance of database manager:
 *  	SQLiteDbManager * mgr = SQLiteDbManager:;GetInstance();
 *  
 *  2. Create a database:
 *  	mgr->Open("C:\\data\\miliao\\test.sqlite");
 *  
 *  3. Create a table:
 *  	mgr->Execute("CREATE TABLE test (name TEXT, age INTEGER, weight FLOAT)");
 *  
 *  4. Insert a record:
 *  	mgr->Execute("INSERT INTO test VALUES ('%s', %d, '%f')", "wanghuan", 25, 111.11);
 *  
 *  5. Update a record:
 *  	mgr->Execute("UPDATE test SET age = %d WHERE name = '%s'", 24, "wanghuan");
 *  
 *  6. Query records:
 *  	SQLiteResultSet rs = mgr->Query("SELECT * FROM test WHERE age > %d AND age < %d", 0, 100);
 *  
 *  7. Iterate result set:
 *  	while (rs.HasNext())
 *  	{
 *  		const char name = rs.GetString("name"); // use column name as key
 *  		int age = rs.GetInt(2);					// use column index as key, start from 1
 *  		double weight = rs.GetFloat(3);
 *  		rs.Next();
 *  	}
 *  
 *  8. Use SQLiteStatement:
 *  	 	SQLiteStatement stmt = mgr->CompileStatement("INSERT INTO test VALUES (?, %d, ?)", 20);
 *  	 	stmt.Bind(1, "king"); //the first parameter, start from 1
 *  	 	stmt.Bind(2, 222.2); 
 *     SQLiteStatement exists mainly for the blob data, which can not be inserted by the above methods
 *     you can use the following statements to insert a blob data
 *     	 	stmt.Bind(idx, (unsigned char * )buf, sizeof(buf)); 
 *  	 	stmt.Execute();
 *     Also you can call Query() on stmt, which returns SQLiteResultSet
 *  		SQLiteResultSet rs = stmt.Query();
 *  
 *  9. Transaction:
 *  	use transaction to manipulate a batch of records.
 *  	mgr->BeginTransaction();
 *  	// your code goes here
 *  	mgr->EndTransaction();
 *  
 *  10. Close the database:
 *  	mgr->Close();
 *  
 */

#include "SQLiteDb.h"
#include "CMichatDb.h"
#include "ylog.h"
#include "ystring.h"

SQLiteResultSet::SQLiteResultSet()
{
    m_pstmt = NULL;
    m_ownStmt = false;
    m_eof = true;
    m_pdb = NULL;
    m_cols = 0;
}

SQLiteResultSet::SQLiteResultSet(const SQLiteResultSet &other)
{
	m_pdb = other.m_pdb;
	m_pstmt = other.m_pstmt;
	m_cols = other.m_cols;
	m_eof = other.m_eof;
	m_ownStmt = other.m_ownStmt;
	if (m_ownStmt)
		const_cast<SQLiteResultSet &>(other).m_ownStmt = false;
}

SQLiteResultSet::SQLiteResultSet(sqlite3 *pdb, sqlite3_stmt * pstmt, bool eof, bool ownStmt)
{
    m_pstmt = pstmt;
    m_ownStmt = ownStmt;
    m_eof = eof;
    m_pdb = pdb;
    m_cols = sqlite3_column_count(pstmt);
}

SQLiteResultSet& SQLiteResultSet::operator=(const SQLiteResultSet &other)
{
	Finalize();
	m_pdb = other.m_pdb;
	m_pstmt = other.m_pstmt;
	m_cols = other.m_cols;
	m_eof = other.m_eof;
	m_ownStmt = other.m_ownStmt;
	if (m_ownStmt)
		const_cast<SQLiteResultSet &>(other).m_ownStmt = false;
	return *this;
}

SQLiteResultSet::~SQLiteResultSet()
{
    Finalize();
}

bool SQLiteResultSet::HasNext()
{
    return !m_eof;
}

void SQLiteResultSet::Next()
{
    int rc = sqlite3_step(m_pstmt);
    if (rc == SQLITE_DONE)
    {
        m_eof = true;
    }
    else if (rc == SQLITE_ROW)
        ;// more rows, nothing to do
    else{
        m_eof = true;
        Finalize();
    }
}

void SQLiteResultSet::Finalize()
{
	if (m_ownStmt && m_pstmt)
	{
		int rc = sqlite3_finalize(m_pstmt);
		m_pstmt = 0;
		m_ownStmt = false;
		if (rc != SQLITE_OK)
		{
			const char * err = sqlite3_errmsg(m_pdb);
			LOG(ERROR) << err;
		}
	}
}

const char * SQLiteResultSet::GetString(int idx, const char * nullValue)
{
    if (FieldDataType(idx) == SQLITE_NULL)
    	return nullValue;
    return (const char *)sqlite3_column_text(m_pstmt, idx - 1);
}

const char * SQLiteResultSet::GetString(const char * colname, const char * nullValue)
{
    return GetString(GetColumnIndex(colname), nullValue);
}

int SQLiteResultSet::GetInt(int idx, int nullValue)
{
    if (FieldDataType(idx) == SQLITE_NULL)
        return nullValue;
    else
        return sqlite3_column_int(m_pstmt, idx - 1);
}

int SQLiteResultSet::GetInt(const char * colname, int nullValue)
{ 
    return GetInt(GetColumnIndex(colname), nullValue);
}

double SQLiteResultSet::GetFloat(int idx, double nullValue)
{
    if (FieldDataType(idx) == SQLITE_NULL)
        return nullValue;
    else
        return sqlite3_column_double(m_pstmt, idx - 1);
}

double SQLiteResultSet::GetFloat(const char * colname, double nullValue)
{ 
    return GetInt(GetColumnIndex(colname), nullValue);
}

unsigned char * SQLiteResultSet::GetBlob(int idx, int &len)
{
    FieldDataType(idx);
    len = sqlite3_column_bytes(m_pstmt, idx - 1);
    return (unsigned char * )sqlite3_column_blob(m_pstmt, idx - 1);
}

unsigned char * SQLiteResultSet::GetBlob(const char * colname, int &len)
{
    return GetBlob(GetColumnIndex(colname), len);
}

int SQLiteResultSet::GetColumnIndex(const char * colname)
{
    for (int i = 0; i < m_cols; i++)
    {
        const char * tmp = sqlite3_column_name(m_pstmt, i);
        if (strcmp(colname, tmp) == 0)
            return i + 1;
    }
    
    return 0;
}

int SQLiteResultSet::FieldDataType(int idx)
{
    LOG_ASSERT(idx >= 1 && idx <= m_cols);
    return sqlite3_column_type(m_pstmt, idx - 1);
}

///////////////////////////////////////////////////////////////

SQLiteStatement::~SQLiteStatement()
{
	Finalize();
}

SQLiteStatement::SQLiteStatement(sqlite3 * pdb, sqlite3_stmt * pstmt)
{
    m_pdb = pdb;
    m_pstmt = pstmt;
}

SQLiteStatement::SQLiteStatement(const SQLiteStatement &other)
{
    m_pdb = other.m_pdb;
    m_pstmt = other.m_pstmt;
    const_cast<SQLiteStatement &>(other).m_pstmt = NULL;
}

SQLiteStatement& SQLiteStatement::operator=(const SQLiteStatement &other)
{
    Finalize();
    m_pdb = other.m_pdb;
    m_pstmt = other.m_pstmt;
    /* only one object can obtain statement */
    const_cast<SQLiteStatement &>(other).m_pstmt = NULL;
    return *this;
}

SQLiteResultSet SQLiteStatement::Query()
{
    int rc = sqlite3_step(m_pstmt);
    if (rc == SQLITE_DONE)
        return SQLiteResultSet(m_pdb, m_pstmt, true /* eof */, false);
    else if (rc == SQLITE_ROW)
        return SQLiteResultSet(m_pdb, m_pstmt, false /* eof */, false);
    else
    {
        rc = sqlite3_reset(m_pstmt);
        const char * err = sqlite3_errmsg(m_pdb);
        LOG(ERROR) << err;
        return SQLiteResultSet();
    }
}

void SQLiteStatement::Execute()
{
    int rc = sqlite3_step(m_pstmt);
    if (rc != SQLITE_DONE && rc != SQLITE_ROW)
    {
        const char * err = sqlite3_errmsg(m_pdb);
        LOG(ERROR) << err;
    }
    sqlite3_reset(m_pstmt);
}

void SQLiteStatement::Bind(int idx, const char * value)
{
    int rc = sqlite3_bind_text(m_pstmt, idx, value, -1, (void *)SQLITE_TRANSIENT);
    HandleResult(rc);
}

void SQLiteStatement::Bind(int idx, const int value)
{
    int rc = sqlite3_bind_int(m_pstmt, idx, value);
    HandleResult(rc);
}

void SQLiteStatement::Bind(int idx, const double value)
{
    int rc = sqlite3_bind_double(m_pstmt, idx, value);
    HandleResult(rc);
}

void SQLiteStatement::Bind(int idx, const unsigned char * value, int len)
{
    int rc = sqlite3_bind_blob(m_pstmt, idx, (const void *)value, len, (void *)SQLITE_TRANSIENT);
    HandleResult(rc);
}

void SQLiteStatement::BindNull(int idx)
{
    int rc = sqlite3_bind_null(m_pstmt, idx);
    HandleResult(rc);
}

void SQLiteStatement::Reset()
{
    if (m_pstmt)
    {
        int rc = sqlite3_reset(m_pstmt);
        HandleResult(rc);
    }
}

void SQLiteStatement::Finalize()
{
    if (m_pstmt)
    {
        int rc = sqlite3_finalize(m_pstmt);
        m_pstmt = 0;
        HandleResult(rc);
    }
}

void SQLiteStatement::HandleResult(int rc)
{
    if (rc != SQLITE_OK)
    {
        const char * err = sqlite3_errmsg(m_pdb);
        LOG(ERROR) << err;
    }
}

///////////////////////////////////////////////////////////////
SQLiteDbManager * SQLiteDbManager::s_instance = NULL;

#define GET_FORMAT_SQL(sql)         \
    static char sql[512];                  \
    va_list argp;                   \
    va_start(argp, format);         \
    vsprintf(sql, format, argp);    \
    va_end(argp);                   \

SQLiteDbManager::SQLiteDbManager()
{
    m_pdb = NULL;
    m_busyTimeout = 60000; // 60 seconds
}

SQLiteDbManager::~SQLiteDbManager()
{
    Close();
}

SQLiteDbManager * SQLiteDbManager::GetInstance()
{
    if (s_instance == NULL)
        s_instance = new SQLiteDbManager();
    return s_instance;
}

void SQLiteDbManager::Open(const char * szFile)
{
    int err = sqlite3_open(szFile, &m_pdb);
    if (err != SQLITE_OK)
    {
        const char* szError = sqlite3_errmsg(m_pdb);
        LOG(ERROR) << szError;
    }
    Execute("pragma synchronous = off");
    SetBusyTimeout(m_busyTimeout);
}

void SQLiteDbManager::Close()
{
    if (m_pdb)
    {
        sqlite3_close(m_pdb);
        m_pdb = NULL;
    }
}

void SQLiteDbManager::SetBusyTimeout(int nMillisecs)
{
    m_busyTimeout = nMillisecs;
    sqlite3_busy_timeout(m_pdb, m_busyTimeout);
}

void SQLiteDbManager::BeginTransaction()
{
	int rc = sqlite3_exec(m_pdb, "begin transaction", NULL, NULL, NULL);
	if (rc != SQLITE_OK)
	{
		LOG(ERROR) << sqlite3_errmsg(m_pdb);
	}
}

void SQLiteDbManager::EndTransaction()
{
	int rc = sqlite3_exec(m_pdb, "end transaction", NULL, NULL, NULL);
	if (rc != SQLITE_OK)
	{
		LOG(ERROR) << sqlite3_errmsg(m_pdb);
	}
}

SQLiteResultSet SQLiteDbManager::Query(const char * format, ...)
{
    GET_FORMAT_SQL(sql);
    
    sqlite3_stmt* pstmt = Compile(sql);
	int rc = sqlite3_step(pstmt);
	
	if (rc == SQLITE_DONE)
	{
	    return SQLiteResultSet(m_pdb, pstmt, true/* eof */, true);
	}else if (rc == SQLITE_ROW)
	{
	    return SQLiteResultSet(m_pdb, pstmt, false/* eof */, true);
	}
	else
	{
	    sqlite3_finalize(pstmt);
	    const char * err = sqlite3_errmsg(m_pdb);
		LOG(ERROR) << err;
		return SQLiteResultSet();
	}
}

bool SQLiteDbManager::IsTableExists(const char * table)
{
    SQLiteResultSet rs = Query("select count(*) from sqlite_master where type = 'table' and name = '%s'"
        , table);
    if (rs.HasNext())
        return true;
    return false;        
}

void SQLiteDbManager::Execute(const char * format, ...)
{
    GET_FORMAT_SQL(sql);
    char * err = 0;
    int rc = sqlite3_exec(m_pdb, sql, 0, 0, &err);
    if (rc != SQLITE_OK)
    {
        LOG(ERROR) << err << ": " << sql;
    }
}

int SQLiteDbManager::GetAffectedRows()
{
    return sqlite3_changes(m_pdb);
}

TInt64 SQLiteDbManager::GetLastRowId()
{
    return sqlite3_last_insert_rowid(m_pdb);
}

SQLiteStatement SQLiteDbManager::CompileStatement(const char * format, ...)
{
	GET_FORMAT_SQL(sql);
    sqlite3_stmt * pstmt = Compile(sql);
    return SQLiteStatement(m_pdb, pstmt);
}

sqlite3_stmt * SQLiteDbManager::Compile(const char * sql)
{
	static char buf[512];
    const char* tail = buf;
    sqlite3_stmt* pstmt;

    int rc = sqlite3_prepare(m_pdb, sql, (int) strlen(sql), &pstmt, &tail);
    
    if (rc != SQLITE_OK)
    {
		const char * err = sqlite3_errmsg(m_pdb);
        LOG(ERROR) << err;
    }
    return pstmt;
}



